﻿CREATE PROCEDURE dbo.s_MovePage_Ex
	(
		@LoanID int,
		@PageID int,
		@ImageTypeID_To int,
		@PageAfterID int -- better go by ID then by PageNumber in case when 1,5,9 is being moved after 3 from s_MovePages_Ex
	)
AS

DECLARE @ImageID_To int,@PageAfterNumber int

BEGIN TRAN

-- Get ID of destination Image Folder
SELECT @ImageID_To = ID FROM t_Image WHERE LoanID = @LoanID AND ImageTypeID = @ImageTypeID_To
IF @ImageID_To IS NULL BEGIN
	INSERT INTO t_Image(LoanID,ImageTypeID)VALUES(@LoanID,@ImageTypeID_To)
	SET @ImageID_To = IDENT_CURRENT('t_Image')
END

UPDATE t_Page SET PageNumber = 100000 WHERE ID = @PageID

SET @PageAfterNumber = ISNULL((SELECT PageNumber FROM t_Page WHERE ID = @PageAfterID),0)

PRINT dbo.sprintf0('@PageAfterNumber:$0',@PageAfterNumber)

UPDATE t_Page SET ImageID = @ImageID_To WHERE ID = @PageID

UPDATE t_Page SET PageNumber = @PageAfterNumber +1 WHERE ID = @PageID

COMMIT TRAN

